# -*- coding:utf-8 -*-

from sqlalchemy import select, update, delete, and_, desc
from sqlalchemy.ext.asyncio import AsyncSession
from module_admin.entity.do.search_task_do import SearchTask
from module_admin.entity.do.order_record_do import OrderRecord
from module_admin.entity.vo.search_task_vo import SearchTaskQueryModel, OrderRecordQueryModel
from typing import Optional, List
from datetime import datetime


class SearchTaskDao:
    """搜索任务数据访问层"""
    
    @classmethod
    async def get_task_by_id(cls, db: AsyncSession, task_id: int) -> Optional[SearchTask]:
        """根据ID获取任务"""
        query = select(SearchTask).where(
            SearchTask.id == task_id,
            SearchTask.del_flag == '0'
        )
        result = await db.execute(query)
        return result.scalars().first()
    
    @classmethod
    async def get_task_list(
        cls,
        db: AsyncSession,
        query_params: SearchTaskQueryModel,
        page_num: int = 1,
        page_size: int = 10,
        current_user_id: int = None,
        is_admin: bool = False,
        filter_user_id: int = None  # 超级管理员切换用户查看（可选）
    ):
        """分页查询任务列表（支持用户隔离）
        
        Args:
            db: 数据库会话
            query_params: 查询参数
            page_num: 页码
            page_size: 每页数量
            current_user_id: 当前用户ID
            is_admin: 是否是管理员（role_id=1的超级管理员）
            filter_user_id: 超级管理员切换用户查看（可选，仅管理员可用）
        """
        conditions = [SearchTask.del_flag == '0']
        
        # ✅ 用户隔离：非管理员只能看到自己创建的任务
        # 如果指定了filter_user_id（超级管理员切换用户），则按filter_user_id过滤
        if filter_user_id is not None and is_admin:
            # 超级管理员切换用户查看
            conditions.append(SearchTask.create_by == filter_user_id)
        elif not is_admin and current_user_id:
            # 非管理员只能看到自己创建的任务
            conditions.append(SearchTask.create_by == current_user_id)
        
        if query_params.task_name:
            conditions.append(SearchTask.task_name.like(f'%{query_params.task_name}%'))
        if query_params.keyword:
            conditions.append(SearchTask.keyword.like(f'%{query_params.keyword}%'))
        if query_params.task_status:
            conditions.append(SearchTask.task_status == query_params.task_status)
        if query_params.status:
            conditions.append(SearchTask.status == query_params.status)
        
        # 查询总数
        count_query = select(SearchTask).where(and_(*conditions))
        count_result = await db.execute(count_query)
        total = len(count_result.scalars().all())
        
        # 分页查询
        query = select(SearchTask).where(and_(*conditions)).order_by(
            desc(SearchTask.create_time)
        ).limit(page_size).offset((page_num - 1) * page_size)
        
        result = await db.execute(query)
        tasks = result.scalars().all()
        
        return tasks, total
    
    @classmethod
    async def get_running_tasks(cls, db: AsyncSession) -> List[SearchTask]:
        """获取所有运行中的任务"""
        query = select(SearchTask).where(
            SearchTask.del_flag == '0',
            SearchTask.task_status == 'running',
            SearchTask.status == '0'
        )
        result = await db.execute(query)
        return result.scalars().all()
    
    @classmethod
    async def get_all_tasks_with_users(cls, db: AsyncSession) -> List[SearchTask]:
        """获取所有任务（用于获取有任务的用户列表）"""
        query = select(SearchTask).where(
            SearchTask.del_flag == '0',
            SearchTask.status == '0'
        )
        result = await db.execute(query)
        return result.scalars().all()
    
    @classmethod
    async def create_task(cls, db: AsyncSession, task: SearchTask) -> SearchTask:
        """创建任务"""
        db.add(task)
        await db.flush()
        await db.refresh(task)
        return task
    
    @classmethod
    async def update_task(cls, db: AsyncSession, task_id: int, update_data: dict):
        """更新任务"""
        query = update(SearchTask).where(
            SearchTask.id == task_id,
            SearchTask.del_flag == '0'
        ).values(**update_data)
        await db.execute(query)
    
    @classmethod
    async def delete_task(cls, db: AsyncSession, task_id: int):
        """删除任务（软删除）"""
        query = update(SearchTask).where(
            SearchTask.id == task_id
        ).values(del_flag='2')
        await db.execute(query)


class OrderRecordDao:
    """下单记录数据访问层"""
    
    @classmethod
    async def get_record_by_id(cls, db: AsyncSession, record_id: int) -> Optional[OrderRecord]:
        """根据ID获取记录"""
        query = select(OrderRecord).where(
            OrderRecord.id == record_id,
            OrderRecord.del_flag == '0'
        )
        result = await db.execute(query)
        return result.scalars().first()
    
    @classmethod
    async def get_pending_record_by_item_id(cls, db: AsyncSession, item_id: str) -> Optional[OrderRecord]:
        """根据商品ID获取pending状态的订单记录（正在下单）"""
        query = select(OrderRecord).where(
            OrderRecord.item_id == item_id,
            OrderRecord.order_status == 'pending',
            OrderRecord.del_flag == '0'
        ).order_by(OrderRecord.create_time.desc())
        result = await db.execute(query)
        return result.scalars().first()
    
    @classmethod
    async def get_record_list(
        cls,
        db: AsyncSession,
        query_params: OrderRecordQueryModel,
        page_num: int = 1,
        page_size: int = 10,
        current_user_id: int = None,
        is_admin: bool = False,
        filter_user_id: int = None  # 超级管理员切换用户查看（可选）
    ):
        """分页查询下单记录（支持用户隔离）
        
        Args:
            db: 数据库会话
            query_params: 查询参数
            page_num: 页码
            page_size: 每页数量
            current_user_id: 当前用户ID
            is_admin: 是否是管理员（role_id=1的超级管理员）
            filter_user_id: 超级管理员切换用户查看（可选，仅管理员可用）
        """
        from sqlalchemy import func, text
        
        # ✅ 修复：只查询存在的字段，兼容数据库表结构
        # ⚠️ 重要：由于使用JOIN，WHERE条件需要加上表别名`o.`
        conditions_sql = ["o.del_flag = '0'"]
        params = {}
        
        # ✅ 用户隔离：非管理员只能看到自己创建的订单
        # 如果指定了filter_user_id（超级管理员切换用户），则按filter_user_id过滤
        if filter_user_id is not None and is_admin:
            # 超级管理员切换用户查看
            conditions_sql.append("o.create_by = :filter_user_id")
            params['filter_user_id'] = filter_user_id
        elif not is_admin and current_user_id:
            # 非管理员只能看到自己创建的订单
            conditions_sql.append("o.create_by = :current_user_id")
            params['current_user_id'] = current_user_id
        
        if query_params.task_id:
            conditions_sql.append("o.task_id = :task_id")
            params['task_id'] = query_params.task_id
        if query_params.item_id:
            conditions_sql.append("o.item_id = :item_id")
            params['item_id'] = query_params.item_id
        if query_params.item_title:
            conditions_sql.append("o.title LIKE :item_title")
            params['item_title'] = f"%{query_params.item_title}%"
        if query_params.client_id:
            conditions_sql.append("o.client_id = :client_id")
            params['client_id'] = query_params.client_id
        if query_params.keyword:
            conditions_sql.append("o.keyword LIKE :keyword")
            params['keyword'] = f"%{query_params.keyword}%"
        if query_params.seller_id:
            conditions_sql.append("o.seller_id = :seller_id")
            params['seller_id'] = query_params.seller_id
        if query_params.order_status:
            conditions_sql.append("o.order_status = :order_status")
            params['order_status'] = query_params.order_status
        if query_params.start_time:
            conditions_sql.append("o.create_time >= :start_time")
            params['start_time'] = query_params.start_time
        if query_params.end_time:
            conditions_sql.append("o.create_time <= :end_time")
            params['end_time'] = query_params.end_time
        
        where_clause = " AND ".join(conditions_sql)
        
        # 查询总数 - 需要JOIN agent_client表
        count_sql = text(f"SELECT COUNT(*) as total FROM order_record o LEFT JOIN agent_client c ON o.client_id = c.client_id WHERE {where_clause}")
        count_result = await db.execute(count_sql, params)
        total = count_result.scalar() or 0
        
        # 分页查询 - 只选择标准字段（按照C# OrderItem结构）
        # ✅ 修复：只查询标准字段，不查询兼容字段
        # ⚠️ 重要：JOIN agent_client表获取客户端备注（remark）
        query_sql = text(f"""
            SELECT 
                o.id, o.item_id, 
                o.title,
                o.price,
                o.pic_url,
                o.client_id, o.order_status, 
                o.create_time as order_time,
                o.error,
                o.is_auto_order,
                o.biz_order_id, o.biz_order_id_str, o.pay_order_id, o.pay_url,
                o.service_order, o.use_pay_url,
                o.account_user_id, o.user_name,
                o.keyword,
                o.seller_id,
                o.user_nick_name,
                o.create_time, o.update_time,
                c.client_name,
                c.remark as client_remark,
                c.account_nickname as client_account_nickname
            FROM order_record o
            LEFT JOIN agent_client c ON o.client_id = c.client_id
            WHERE {where_clause}
            ORDER BY o.create_time DESC
            LIMIT :limit OFFSET :offset
        """)
        params['limit'] = page_size
        params['offset'] = (page_num - 1) * page_size
        
        result = await db.execute(query_sql, params)
        rows = result.fetchall()
        
        # 转换为OrderRecord对象（手动构造）
        records = []
        for row in rows:
            # 创建一个字典来模拟OrderRecord对象（按照C# OrderItem结构）
            # ⚠️ 重要：字段名需要匹配前端期望的格式
            # ✅ 修复：格式化价格（DECIMAL转为字符串，保留两位小数）
            price_value = getattr(row, 'price', None)
            price_str = None
            if price_value is not None:
                try:
                    price_str = f"{float(price_value):.2f}"
                except (ValueError, TypeError):
                    price_str = str(price_value) if price_value else None
            
            # ✅ 处理is_auto_order字段（Boolean类型）
            is_auto_order_value = getattr(row, 'is_auto_order', None)
            if is_auto_order_value is None:
                is_auto_order_value = True  # 默认值
            elif isinstance(is_auto_order_value, (int, str)):
                # MySQL可能返回0/1或'0'/'1'
                is_auto_order_value = bool(int(is_auto_order_value))
            
            # ✅ 统一使用直接属性访问（SQLAlchemy Row对象支持属性访问）
            # 使用 getattr 作为后备，以防字段不存在
            pic_url_value = getattr(row, 'pic_url', None)  # 直接获取值，保留原值（None、空字符串或URL）
            
            record_dict = {
                'id': row.id,
                'item_id': row.item_id,
                'title': getattr(row, 'title', '') or '',  # 保留原字段
                'item_title': getattr(row, 'title', '') or '',  # 前端期望的字段名
                'price': price_value,  # 保留原字段（DECIMAL类型）
                'item_price': price_str,  # 前端期望的字段名（字符串格式）
                'pic_url': pic_url_value,  # ✅ 商品图片URL（保留原值，None或空字符串或URL）
                'client_id': getattr(row, 'client_id', ''),
                'client_name': getattr(row, 'client_name', '') or '',  # ✅ 客户端名称（从agent_client表JOIN）
                'client_remark': getattr(row, 'client_remark', None),  # 客户端备注
                'keyword': getattr(row, 'keyword', None),
                'order_status': getattr(row, 'order_status', 'pending'),
                'order_time': getattr(row, 'order_time', None),
                'error': getattr(row, 'error', None),  # 保留原字段
                'error_msg': getattr(row, 'error', None),  # 前端期望的字段名
                'is_auto_order': is_auto_order_value,  # ✅ 是否自动下单
                'biz_order_id': getattr(row, 'biz_order_id', '') or None,
                'biz_order_id_str': getattr(row, 'biz_order_id_str', '') or None,
                'pay_order_id': getattr(row, 'pay_order_id', '') or None,
                'pay_url': getattr(row, 'pay_url', '') or None,
                'service_order': getattr(row, 'service_order', '') or None,
                'use_pay_url': getattr(row, 'use_pay_url', '') or None,
                'account_user_id': getattr(row, 'account_user_id', '') or None,
                'seller_id': getattr(row, 'seller_id', '') or None,
                'seller_name': getattr(row, 'user_nick_name', '') or None,
                'user_name': getattr(row, 'user_name', '') or None,
                'create_time': getattr(row, 'create_time', None),
                'update_time': getattr(row, 'update_time', None)
            }
            # 使用type创建动态对象
            record = type('OrderRecord', (), record_dict)()
            records.append(record)
        
        return records, total
    
    @classmethod
    async def create_record(cls, db: AsyncSession, record: OrderRecord) -> OrderRecord:
        """创建下单记录"""
        db.add(record)
        await db.flush()
        await db.refresh(record)
        return record
    
    @classmethod
    async def update_record(cls, db: AsyncSession, record_id: int, update_data: dict):
        """更新下单记录"""
        query = update(OrderRecord).where(
            OrderRecord.id == record_id,
            OrderRecord.del_flag == '0'
        ).values(**update_data)
        await db.execute(query)
    
    @classmethod
    async def delete_record(cls, db: AsyncSession, record_id: int):
        """删除下单记录（直接从数据库删除）"""
        from sqlalchemy import delete
        query = delete(OrderRecord).where(OrderRecord.id == record_id)
        await db.execute(query)

